﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Ccit.SysPlat.Log;
using Ccit.Utils;
using System.Text;
using System.Data;
using Ccit.ClassLibrary;

namespace Ccit.SysPlat.Purview
{
    /// <summary>
    /// Description: 系统平台权限菜单 -> 菜单管理SQL SERVER数据实现层
    /// Author: tim 2010-03-02
    /// </summary>
    public class MenuSQL : IMenu
    {
        #region init error log interface
        private ILog _logInterface = null;
        private const string _funcPageName = "菜单管理";
        public MenuSQL()
        {
            _logInterface = Factory.CreateObject(MsErrLog.assemblyName, MsErrLog.className, 
                                                    ImportComponentType.DotNetDll, null) as ILog;
        }        
        #endregion

        #region IMenu 成员

        public List<string> Delete(string menuId)
        {
            if (string.IsNullOrEmpty(menuId)) return new List<string> { "-999", "操作失败:缺少必要参数!" };

            bool bHasChild = isHasChild(menuId);
            if (bHasChild) return new List<string> { "-998", "包含下级子菜单,无法删除!" };

            List<string> chkUsedResult = isUsed(menuId);
            if (chkUsedResult[0] != "0") return new List<string> { "-997", chkUsedResult[1] };

            string cmdText = "DELETE FROM PUR_B1 WHERE PUR_B1_30 LIKE '' + (SELECT PUR_B1_30 AS LAYCODE FROM PUR_B1 WHERE PUR_B1_10='" + menuId + "') + '%'";
            try {
                int iexe = DataAccessor.ExecuteNonQuery(cmdText);
                if (iexe > 0) return new List<string> { menuId, "删除成功!" };
                else return new List<string> { "-996", "操作失败:服务器忙请稍后再试!" };
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + cmdText + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
                return new List<string> { "-995", "操作失败:详情请参见错误日志!" };
            }
        }

        public string GetMenuLevelStruct(string menuId)
        {
            if (string.IsNullOrEmpty(menuId)) return string.Empty;

            MsMenu msMenu = getMenuObject(menuId);  //获取菜单消息对象
            if (null == msMenu) return string.Empty;

            string layCode = msMenu.layCode;
            int layCodeLen = layCode.Length;
            int iLevel = layCodeLen / 6;

            string whereClause = " 1=1 AND PUR_B1_140='0' ";
            string subClause = string.Empty;
            string qryClause = string.Empty;
            for (int i = (iLevel - 1); i >= 0; i--)
            {
                subClause = "SUBSTRING('" + layCode + "',1," + (layCodeLen - i * 6).ToString() + ")";
                qryClause += (string.IsNullOrEmpty(qryClause)) ? subClause : "," + subClause;
            }

            whereClause += (!string.IsNullOrEmpty(qryClause)) ? "AND PUR_B1_30 IN (" + qryClause + ")" : "AND 1>1";
            StringBuilder sbCmdText = new StringBuilder();
            sbCmdText.Append("SELECT PUR_B1_10 AS MENUID FROM PUR_B1 WHERE").Append(whereClause);
            sbCmdText.Append(" ORDER BY PUR_B1_30 ASC");

            string structIds = string.Empty;
            try
            {
                string tempMenuId = string.Empty;
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    while (iReader.Read())
                    {
                        tempMenuId = iReader["MENUID"].ToString();
                        structIds += (string.IsNullOrEmpty(structIds)) ? tempMenuId : "," + tempMenuId;
                    }
                }
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }

            msMenu = null;
            sbCmdText = null;
            return structIds;
        }

        public List<MsMenu> GetStairMenuList()
        {
            StringBuilder sbCmdText = new StringBuilder();
            sbCmdText.Append("SELECT A.PUR_B1_10 AS MENUID,A.PUR_B1_20 AS MENUNAME,A.PUR_B1_80 AS SYSSIGN,A.PUR_B1_160 AS RECORITYPE,");
            sbCmdText.Append("ISNULL(C.MENUCOUNT,0) AS MENUCOUNT FROM PUR_B1 A ");
            sbCmdText.Append("INNER JOIN (SELECT SUBSTRING(PUR_B1_30,1,6) AS LAYCODE,COUNT(*) AS MENUCOUNT FROM PUR_B1 ");
            sbCmdText.Append("WHERE PUR_B1_140='0' GROUP BY  SUBSTRING(PUR_B1_30,1,6)) C ON A.PUR_B1_30=C.LAYCODE ");
            sbCmdText.Append("WHERE PUR_B1_140='0' AND LEN(A.PUR_B1_30)=6 ");
            sbCmdText.Append("ORDER BY A.PUR_B1_30 ASC");

            List<MsMenu> lstMenu = new List<MsMenu>();
            try {
                MsMenu msMenu;
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    while (iReader.Read())
                    {
                        msMenu = new MsMenu {
                            menuId = iReader["MENUID"].ToString(),
                            menuName = iReader["MENUNAME"].ToString(),
                            sysSign = iReader["SYSSIGN"].ToString(),
                            oriType = iReader["RECORITYPE"].ToString(),
                            isHasChild = (Int32.Parse(iReader["MENUCOUNT"].ToString()) > 1) ? "1" : "0"  //有无下级子节点
                        };
                        lstMenu.Add(msMenu);
                    }
                }
            }
            catch (FormatException ex)
            {
                MsErrLog msErrorLog = new MsErrLog {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "002", //数据类型转换异常
                    paramInfo = string.Empty
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }

            sbCmdText = null;
            return lstMenu;
        }

        public List<MsMenu> GetSubMenuList(string menuId,string sourceType)
        {
            if (string.IsNullOrEmpty(menuId)) return new List<MsMenu>();

            StringBuilder sbCmdText = new StringBuilder();
            sbCmdText.Append("SELECT A.PUR_B1_10 AS MENUID,A.PUR_B1_20 AS MENUNAME,A.PUR_B1_80 AS SYSSIGN,A.PUR_B1_160 AS ORITYPE,");
            sbCmdText.Append("ISNULL(C.MENUCOUNT,0) AS MENUCOUNT,(LEN(A.PUR_B1_30) / 6) AS LAY FROM PUR_B1 A ");
            sbCmdText.Append("INNER JOIN (SELECT PUR_B1_30 AS LAYCODE FROM PUR_B1 WHERE PUR_B1_10='").Append(menuId).Append("') D ON A.PUR_B1_30 LIKE '' + D.LAYCODE + '%' ");
            sbCmdText.Append("INNER JOIN (SELECT SUBSTRING(A.PUR_B1_30,1,(LEN(D.LAYCODE) + 6)) AS LAYCODE,COUNT(*) AS MENUCOUNT ");
            sbCmdText.Append("FROM PUR_B1 A ");
            sbCmdText.Append("INNER JOIN (SELECT PUR_B1_30 AS LAYCODE FROM PUR_B1 WHERE PUR_B1_10='").Append(menuId).Append("') D ON A.PUR_B1_30 LIKE '' + D.LAYCODE + '%' ");
            sbCmdText.Append("WHERE A.PUR_B1_140='0' AND (LEN(A.PUR_B1_30) >= (LEN(D.LAYCODE) + 6)) AND (LEN(A.PUR_B1_30) <= (LEN(D.LAYCODE) + 12)) ");
            sbCmdText.Append("GROUP BY SUBSTRING(A.PUR_B1_30,1,(LEN(D.LAYCODE) + 6))) C ON C.LAYCODE=A.PUR_B1_30 ");
            sbCmdText.Append("WHERE A.PUR_B1_140='0' AND LEN(A.PUR_B1_30) = (LEN(D.LAYCODE) + 6) ");
            sbCmdText.Append("ORDER BY A.PUR_B1_40 ASC");

            List<MsMenu> lstMenu = new List<MsMenu>();
            try {
                MsMenu msMenu;
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    while (iReader.Read())
                    {
                        msMenu = new MsMenu
                        {
                            menuId = iReader["MENUID"].ToString(),
                            menuName = iReader["MENUNAME"].ToString(),
                            sysSign = iReader["SYSSIGN"].ToString(),
                            oriType = iReader["ORITYPE"].ToString(),
                            lay = iReader["LAY"].ToString(),
                            sourceType = sourceType,
                            isHasChild = (Int32.Parse(iReader["MENUCOUNT"].ToString()) > 1) ? "1" : "0"
                        };
                        lstMenu.Add(msMenu);
                    }
                }
                
            }
            catch (FormatException ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "002", //数据类型转换异常
                    paramInfo = string.Empty
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }

            sbCmdText = null;
            return lstMenu;
        }

        public List<string> Save(MsMenu msMenu)
        {
            if (null == msMenu) return new List<string> { "-999", "操作失败:缺少必要参数!" };
            string menuId = msMenu.menuId;
            if (string.IsNullOrEmpty(menuId)) return addnew(msMenu);
            else return update(msMenu);
        }

        public List<MsKeyValue> GetALLMenus(string filterMenuId, string sysParam)
        {
            string whereClause = " 1=1";

            if(string.IsNullOrEmpty(sysParam) || sysParam != "999")
                whereClause += " AND PUR_B1_80='0'";

            if (!string.IsNullOrEmpty(filterMenuId))
            {
                string qryCon = " AND PUR_B1_30 NOT LIKE '' + (SELECT PUR_B1_30 FROM PUR_B1 WHERE PUR_B1_10='" + filterMenuId + "') + '%'";
                whereClause += qryCon;
            }

            StringBuilder sbCmdText = new StringBuilder();
            sbCmdText.Append("SELECT PUR_B1_10 AS MENUID,PUR_B1_20 AS MENUNAME,PUR_B1_30 AS LAYCODE FROM PUR_B1 ");
            sbCmdText.Append("WHERE").Append(whereClause).Append(" AND LEN(PUR_B1_30) <= 12 ORDER BY PUR_B1_30 ASC,PUR_B1_40 ASC");

            List<MsKeyValue> lstMenu = new List<MsKeyValue>();
            try {
                MsKeyValue msMenu;
                string layCode = string.Empty;
                int bmLen = 0;
                string indPrefix = string.Empty; //缩进前缀
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    while (iReader.Read())
                    {
                        layCode = iReader["LAYCODE"].ToString();
                        bmLen = layCode.Length / 6;
                        indPrefix = string.Empty;
                        for (int j = 0; j < bmLen - 1; j++)
                        {
                            indPrefix += "\u3000";
                        }

                        msMenu = new MsKeyValue {
                            key = iReader["MENUID"].ToString(),
                            val = indPrefix + iReader["MENUNAME"].ToString()
                        };
                        lstMenu.Add(msMenu);
                    }                    
                }
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }

            sbCmdText = null;
            return lstMenu;
        }

        public MsMenu GetMenuInfo(string menuId)
        {
            if (string.IsNullOrEmpty(menuId)) return null;

            return getMenuObject(menuId);
        }
        
        public List<string> IsCanSave(MsMenu msMenu)
        {
            string upMenuId = msMenu.upMenuId;
            string menuName = msMenu.menuName;
            if (string.IsNullOrEmpty(upMenuId)) return new List<string> { "1", "校验正确,可以进行保存操作!" };

            string cmdText = string.Empty;
            try {
                MsMenu msUpMenu = getMenuObject(upMenuId);
                string upMenuLayCode = msUpMenu.layCode;
                string upMenuName = msUpMenu.menuName;

                int lay = upMenuLayCode.Length / 6;
                int subMenuLen = 0;
                string dbMenuLayCode = string.Empty;
                string errMsg = string.Empty;
                if (lay <= 2)
                {
                    MsMenu msDbMenu = getMenuObject(msMenu.menuId);
                    dbMenuLayCode = msDbMenu.layCode;
                    subMenuLen = (lay == 1) ? dbMenuLayCode.Length + 12 : dbMenuLayCode.Length + 6;
                    cmdText = "SELECT COUNT(PUR_B1_10) AS SUBMENUCOUNT FROM PUR_B1 WHERE PUR_B1_30 LIKE '" + dbMenuLayCode;
                    cmdText += "%' AND LEN(PUR_B1_30)=" + subMenuLen.ToString() + " AND PUR_B1_140='0'";

                    int subMenuCount = (Int32)DataAccessor.ExecuteScalar(cmdText);
                    if (subMenuCount > 0)
                    {                       
                        if (lay == 1)
                        {
                            errMsg = "当前菜单【" + menuName + "】包含有2级子菜单。" +
                              "\r\n如果将菜单移动到【" + upMenuName + "】下，则其2级以及后续子菜单将成为无效菜单!\r\n忽略警告继续保存操作点击确定;否则点击取消按钮!";
                        }
                        else {
                            errMsg = "当前菜单【" + menuName + "】包含有1级子菜单。" +
                              "\r\n如果将菜单移动到【" + upMenuName + "】下，则其1级以及后续子菜单将成为无效菜单!\r\n忽略警告继续保存操作点击确定;否则点击取消按钮!";
                        }

                        return new List<string> { "-990", errMsg };
                    }
                    else
                        return new List<string> { "1", "校验正确,可以进行保存操作!" };

                }
                else {
                    errMsg = "上级菜单【" + upMenuName + "】已经是第三级子菜单，不能再添加子菜单!";
                    return new List<string> { "-990", errMsg };
                }
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + cmdText + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
                return new List<string> { "-991", "保存校验失败:详情请参见错误日志" };
            }
        }

        #endregion

        #region private methods
        /// <summary>
        /// 判断当前菜单是否包含下级子菜单
        /// </summary>
        /// <param name="menuId"></param>
        /// <returns>true / false</returns>
        private bool isHasChild(string menuId)
        {
            StringBuilder sbCmdText = new StringBuilder();
            sbCmdText.Append("SELECT SUBSTRING(A.PUR_B1_30,1,LEN(D.LAYCODE)) AS LAYCODE,COUNT(*) AS MENUCOUNT ");
            sbCmdText.Append("FROM PUR_B1 A ");
            sbCmdText.Append("INNER JOIN (SELECT PUR_B1_30 AS LAYCODE FROM PUR_B1 WHERE PUR_B1_10='").Append(menuId).Append("') D ON A.PUR_B1_30 LIKE '' + D.LAYCODE + '%' ");
            sbCmdText.Append("WHERE  A.PUR_B1_140='0' AND (LEN(A.PUR_B1_30) >= LEN(D.LAYCODE)) AND (LEN(A.PUR_B1_30) <= (LEN(D.LAYCODE) + 6)) ");
            sbCmdText.Append("GROUP BY SUBSTRING(A.PUR_B1_30,1,LEN(D.LAYCODE))");

            bool bHas = true;
            string menuCount = "1";
            try {                
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    if (iReader.Read() && null != iReader[0]) menuCount = iReader["MENUCOUNT"].ToString();
                }

                bHas = (Int32.Parse(menuCount) > 1) ? true : false;            
            }
            catch (FormatException ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "002", //数据类型转换异常
                    paramInfo = menuCount
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }

            return bHas;
        }

        /// <summary>
        /// 检查当前菜单是否绑定了角色
        /// </summary>
        /// <param name="menuId"></param>
        /// <returns>
        ///     【0】: 0: 未绑定 1:已绑定 -997: Exception
        ///     【1】: 消息
        /// </returns>
        private List<string> isUsed(string menuId)
        {
            StringBuilder sbCmdText = new StringBuilder();
            sbCmdText.Append("SELECT DISTINCT(A.PUR_A1_20) AS ROLENAME FROM PUR_A1 A ");
            sbCmdText.Append("LEFT JOIN PUR_R2 B ON A.PUR_A1_10=B.PUR_R2_10 ");
            sbCmdText.Append("LEFT JOIN PUR_R3 C ON A.PUR_A1_10=C.PUR_R3_10 ");
            sbCmdText.Append("WHERE B.PUR_R2_20='").Append(menuId).Append("' OR C.PUR_R3_20='").Append(menuId).Append("'");
                        
            try {
                string used = "0";
                StringBuilder sbMsg = new StringBuilder("不能删除当前菜单!\r\n详细信息:已为下列角色:");     
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    while (iReader.Read())
                    {
                        if (used == "0") used = "1";
                        sbMsg.Append("\r\n【").Append(iReader["ROLENAME"].ToString()).Append("】");
                    }
                }
                sbMsg.Append("\r\n分配了菜单权限.");
                return new List<string> { used, sbMsg.ToString() };
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
                return new List<string> { "-997", "操作失败:详情请参见错误日志!" };
            }
        }


        private MsMenu getMenuObject(string menuId)
        {
            StringBuilder sbCmdText = new StringBuilder();
            sbCmdText.Append("SELECT A.PUR_B1_10 AS MENUID,A.PUR_B1_20 AS MENUNAME,A.PUR_B1_30 AS LAYCODE,A.PUR_B1_40 AS ORDERNO,");
            sbCmdText.Append("A.PUR_B1_50 AS EFFECT,A.PUR_B1_60 AS SHOWSIGN,A.PUR_B1_70 AS MENUDESC,A.PUR_B1_80 AS SYSSIGN,");
            sbCmdText.Append("A.PUR_B1_90 AS ISLEAF,A.PUR_B1_100 AS PURVIEWSET,A.PUR_B1_110 AS PAGEPATH,A.PUR_B1_120 AS RANGESET,");
            sbCmdText.Append("A.PUR_B1_130 AS IMGPATH,A.PUR_B1_140 AS MENUTYPE,A.PUR_B1_150 AS MENUTAG,A.PUR_B1_160 AS ORITYPE,");
            sbCmdText.Append("A.PUR_B1_170 AS FORMID,E.PUR_B1_10 AS UPMENUID FROM PUR_B1 A ");
            sbCmdText.Append("LEFT JOIN PUR_B1 E ON SUBSTRING(A.PUR_B1_30,1,(LEN(A.PUR_B1_30) - 6))=E.PUR_B1_30 ");
            sbCmdText.Append("WHERE A.PUR_B1_10='").Append(menuId).Append("'");

            MsMenu msMenu = null;
            try {
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    while (iReader.Read())
                    {
                        msMenu = new MsMenu {
                            menuId = iReader["MENUID"].ToString(),
                            menuName = iReader["MENUNAME"].ToString(),
                            layCode = iReader["LAYCODE"].ToString(),
                            orderNo = iReader["ORDERNO"].ToString(),
                            effect = iReader["EFFECT"].ToString(),
                            showSign = iReader["SHOWSIGN"].ToString(),
                            memo = iReader["MENUDESC"].ToString(),
                            sysSign = iReader["SYSSIGN"].ToString(),
                            isLeaf = iReader["ISLEAF"].ToString(),
                            purviewSet = iReader["PURVIEWSET"].ToString(),
                            pagePath = iReader["PAGEPATH"].ToString(),
                            rangeSet = iReader["RANGESET"].ToString(),
                            imgPath = iReader["IMGPATH"].ToString(),
                            menuType = iReader["MENUTYPE"].ToString(),
                            menuTag = iReader["MENUTAG"].ToString(),
                            oriType = iReader["ORITYPE"].ToString(),
                            formId = iReader["FORMID"].ToString(),
                            upMenuId = iReader["UPMENUID"].ToString()
                        };                        
                    }
                }
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }
            return msMenu;
        }

        /// <summary>
        /// 新增菜单
        /// </summary>
        /// <param name="msMenu"></param>
        /// <returns>
        ///     【0】: 操作结果 newRecId / 负数
        ///     【1】: 结果消息
        ///     【2】: 菜单名称
        ///     【3】: 上级菜单Id
        ///     【4】: 是否有下级子菜单标志
        ///     【5】: 系统菜单标志
        ///     【6】: 层级 1/2/3
        ///     【7】: 记录来源标志
        /// </returns>
        private List<string> addnew(MsMenu msMenu)
        {
            StringBuilder sbCmdText = new StringBuilder();
            string[] sessionSQL = SessionExt.GetSysFieldSQL("PUR_B1");
            sbCmdText.Append("INSERT INTO PUR_B1(PUR_B1_10,PUR_B1_20,PUR_B1_30,PUR_B1_40,PUR_B1_50,PUR_B1_60,PUR_B1_70,PUR_B1_80,");
            sbCmdText.Append("PUR_B1_90,PUR_B1_100,PUR_B1_110,PUR_B1_120,PUR_B1_130,PUR_B1_140,PUR_B1_150,PUR_B1_160,PUR_B1_170,");
            sbCmdText.Append(sessionSQL[0]).Append(") VALUES (");

            string upMenuId = msMenu.upMenuId;
            #region  获取菜单编码
            string menuLayCode = string.Empty;  //菜单层级编码
            if (string.IsNullOrEmpty(upMenuId))
                menuLayCode = DataInterface.GetStairLayCode("PUR_B1", "PUR_B1_30", 6, string.Empty);
            else
                menuLayCode = DataInterface.GetNewSubLayCode("PUR_B1", "PUR_B1_30", 6, upMenuId, "PUR_B1_10", string.Empty);
            #endregion

            #region 获取顺序号
            string orderNo = msMenu.orderNo;
            int newOrder = 0;
            if (string.IsNullOrEmpty(orderNo))
            {
                newOrder = DataInterface.GetMaxId("PUR_B1", "PUR_B1_40");
                orderNo = newOrder.ToString();
            }
            #endregion

            int newRecId = DataInterface.GetMaxId("PUR_B1", "PUR_B1_10");
            string pagePath = dealPagePath(newRecId.ToString(), msMenu.pagePath);  //编码菜单页面路径的参数

            sbCmdText.Append("'").Append(newRecId.ToString()).Append("',");
            sbCmdText.Append("'").Append(msMenu.menuName.Replace("'", "''")).Append("',");
            sbCmdText.Append("'").Append(menuLayCode).Append("',");
            sbCmdText.Append("'").Append(orderNo).Append("',");
            sbCmdText.Append("'").Append(msMenu.effect).Append("',");
            sbCmdText.Append("'").Append(msMenu.showSign).Append("',");
            sbCmdText.Append("'").Append(msMenu.memo.Replace("'", "''")).Append("',");
            sbCmdText.Append("'").Append(msMenu.sysSign).Append("',");
            sbCmdText.Append("'").Append(msMenu.isLeaf).Append("',");
            sbCmdText.Append("'").Append(msMenu.purviewSet).Append("',");
            sbCmdText.Append("'").Append(pagePath.Replace("'", "''")).Append("',");
            sbCmdText.Append("'").Append(msMenu.rangeSet).Append("',");
            sbCmdText.Append("'").Append(msMenu.imgPath.Replace("'", "''")).Append("',");
            sbCmdText.Append("'").Append(msMenu.menuType).Append("',");
            sbCmdText.Append("'").Append(msMenu.menuTag).Append("',");
            sbCmdText.Append("'").Append(msMenu.oriType).Append("',");
            sbCmdText.Append("'").Append(msMenu.formId).Append("',");
            sbCmdText.Append(sessionSQL[1]).Append(")");

            try {
                int iexe = DataAccessor.ExecuteNonQuery(sbCmdText.ToString());
                if (iexe > 0) {
                    return new List<string> { 
                        newRecId.ToString(), 
                        "保存成功!", 
                        msMenu.menuName,
                        upMenuId, 
                        "0", 
                        msMenu.sysSign,
                        (menuLayCode.Length / 6).ToString(), 
                        msMenu.oriType 
                    };
                }
                else
                {
                    return new List<string> { 
                        "-1",
                        "保存失败:服务器忙请稍后再试!"                       
                    };
                }
            }
            catch (FormatException ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "002", //数据类型转换异常
                    paramInfo = menuLayCode
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
                return new List<string> {
                    "-2",
                    "保存失败:详情请参加错误日志!"                   
                };
            }
            catch(Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
                return new List<string> {
                    "-2",
                    "保存失败:详情请参加错误日志!"                    
                };
            }
        }


        private string dealPagePath(string menuId, string oriPath)
        {
            string encodePath = oriPath;
            if (string.IsNullOrEmpty(oriPath)) return encodePath;

            int questIndex = oriPath.IndexOf("?");

            string reqPage = string.Empty;
            string param = string.Empty;

            if (questIndex != -1 && (questIndex + 1) < oriPath.Length)
            {
                reqPage = oriPath.Substring(0, questIndex + 1);
                param = oriPath.Substring(questIndex + 1) + "&TABID=" + menuId;
                param = Base64Lib.Encode(param);  // encode path
                encodePath = reqPage + param;
            }
            else
            {
                param = "TABID=" + menuId;
                param = Base64Lib.Encode(param);
                encodePath = oriPath + "?" + param;
            }

            return encodePath;
        }

        /// <summary>
        /// 更新菜单记录
        /// </summary>
        /// <param name="msMenu"></param>
        /// <returns>
        ///     【0】: 操作结果 menuId / 负数
        ///     【1】: 结果消息
        ///     【2】: 菜单名称
        ///     【3】: 上级菜单Id
        ///     【4】: 是否有下级子菜单标志
        ///     【5】: 系统菜单标志
        ///     【6】: 层级 1/2/3
        ///     【7】: 记录来源标志
        /// </returns>
        private List<string> update(MsMenu msMenu)
        {            
            string menuId = msMenu.menuId;
            string upMenuId = msMenu.upMenuId;
            string cmdText = "SELECT PUR_B1_10 AS MENUID,PUR_B1_30 AS LAYCODE FROM PUR_B1 WHERE PUR_B1_10='" + menuId + "'";

            MsMenu msCurMenu = null;  //当前菜单的数据库信息
            string menuLayCode = string.Empty;
            StringBuilder sbCmdText = new StringBuilder();
            List<string> lstCmdText = new List<string>();
            sbCmdText.Append("UPDATE PUR_B1 SET PUR_B1_20='").Append(msMenu.menuName.Replace("'", "''")).Append("'");
            try
            {
                #region 获取当前菜单的原层级编码
                using (IDataReader iReader = DataAccessor.ExecuteReader(cmdText))
                {
                    if (iReader.Read() && null != iReader[0])
                    {
                        msCurMenu = new MsMenu
                        {
                            menuId = iReader["MENUID"].ToString(),
                            layCode = iReader["LAYCODE"].ToString()
                        };
                    }
                }
                #endregion

                #region 更新菜单信息
                List<bool> lstAlter = isAlterUpMenu(menuId, upMenuId); //校验上级菜单是否变更
                if (!lstAlter[0]) return new List<string> { "-1", "操作失败:详情请参见错误日志!" };
                else
                {
                    if (lstAlter[1])  //上级菜单发生改变
                    {
                        menuLayCode = (string.IsNullOrEmpty(upMenuId)) ? DataInterface.GetStairLayCode("PUR_B1", "PUR_B1_30", 6, string.Empty)
                                         : DataInterface.GetNewSubLayCode("PUR_B1", "PUR_B1_30", 6, upMenuId, "PUR_B1_10", string.Empty);
                        sbCmdText.Append(",PUR_B1_30='").Append(menuLayCode).Append("'");
                    }
                }              
                string orderNo = msMenu.orderNo;
                if (!string.IsNullOrEmpty(orderNo))
                    sbCmdText.Append(",PUR_B1_40='").Append(orderNo).Append("'");
                sbCmdText.Append(",PUR_B1_50='").Append(msMenu.effect).Append("'");
                sbCmdText.Append(",PUR_B1_60='").Append(msMenu.showSign).Append("'");
                sbCmdText.Append(",PUR_B1_70='").Append(msMenu.memo.Replace("'", "''")).Append("'");
                sbCmdText.Append(",PUR_B1_90='").Append(msMenu.isLeaf).Append("'");
                sbCmdText.Append(",PUR_B1_100='").Append(msMenu.purviewSet).Append("'");
                string pagePath = dealPagePath(menuId, msMenu.pagePath);  //编码菜单页面路径的参数
                sbCmdText.Append(",PUR_B1_110='").Append(pagePath.Replace("'", "''")).Append("'");
                sbCmdText.Append(",PUR_B1_120='").Append(msMenu.rangeSet).Append("'");
                sbCmdText.Append(",PUR_B1_130='").Append(msMenu.imgPath.Replace("'", "''")).Append("'");
                sbCmdText.Append(" WHERE PUR_B1_10='").Append(menuId).Append("'");
                lstCmdText.Add(sbCmdText.ToString());
                #endregion

                #region 变更所有子菜单编码
                if (lstAlter[1])  //上级菜单发生改变
                {
                    sbCmdText.Remove(0, sbCmdText.ToString().Length);
                    int newLayCodeLen = menuLayCode.Length; //菜单新层级编码长度
                    string oriLayCode = msCurMenu.layCode;  //菜单原层级编码
                    int oriLayCodeLen = oriLayCode.Length;  //原层级编码长度
                    sbCmdText.Append("UPDATE PUR_B1 SET PUR_B1_30=('").Append(menuLayCode).Append("' + SUBSTRING(PUR_B1_30,").Append((oriLayCodeLen + 1).ToString()).Append(",LEN(PUR_B1_30))) ");
                    sbCmdText.Append("WHERE PUR_B1_10 IN (SELECT PUR_B1_10 FROM PUR_B1 WHERE PUR_B1_30 LIKE '").Append(oriLayCode).Append("%') AND LEN(PUR_B1_30) > ").Append(oriLayCodeLen.ToString());
                    lstCmdText.Add(sbCmdText.ToString());
                }
                #endregion
                
                int iexe = DataAccessor.ExecuteNonQuery(lstCmdText);
                if (iexe > 0)
                {                    
                    #region 获取菜单是否有下级节点以及部门显示顺序属性
                    sbCmdText.Remove(0, sbCmdText.ToString().Length);
                    sbCmdText.Append("SELECT A.PUR_B1_10 AS MENUID,A.PUR_B1_160 AS ORITYPE,C.MENUCOUNT ");
                    sbCmdText.Append("FROM PUR_B1 A INNER JOIN (");
                    sbCmdText.Append("SELECT SUBSTRING(A1.PUR_B1_30,1,LEN(A2.LAYCODE)) AS LAYCODE,COUNT(*) AS MENUCOUNT FROM PUR_B1 A1 ");
                    sbCmdText.Append("INNER JOIN (SELECT PUR_B1_30 AS LAYCODE FROM PUR_B1 WHERE PUR_B1_10='").Append(menuId).Append("') A2 ON A1.PUR_B1_30 LIKE '' + A2.LAYCODE + '%' ");
                    sbCmdText.Append("GROUP BY SUBSTRING(A1.PUR_B1_30,1,LEN(A2.LAYCODE)) ");
                    sbCmdText.Append(") C ON C.LAYCODE=A.PUR_B1_30");

                    #region 为了记入错误日志
                    lstCmdText.Clear();
                    lstCmdText.Add(sbCmdText.ToString());
                    #endregion

                    string isHasChild = "0";
                    string oriType = string.Empty;
                    using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                    {
                        if (iReader.Read() && null != iReader[0])
                        {
                            isHasChild = (Int32.Parse(iReader["MENUCOUNT"].ToString()) > 1) ? "1" : "0";    
                            oriType = iReader["ORITYPE"].ToString();
                        }
                    }

                    string lay = (menuLayCode.Length / 6).ToString();
                    return new List<string> {
                        menuId,
                        "保存成功!",
                        msMenu.menuName,
                        upMenuId,
                        isHasChild,
                        msMenu.sysSign,
                        lay,
                        oriType
                    };

                    #endregion
                }
                else return new List<string> { 
                    "-1",
                    "保存失败:服务器忙请稍后重新尝试操作!"                   
                };
            }
            catch (FormatException ex)
            {
                MsErrLog msErrorLog = new MsErrLog {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "002", //数据类型转换异常
                    paramInfo = menuLayCode
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
                return new List<string> { 
                    "-2",
                    "保存失败:详情请参见错误日志!"                    
                };
            }
            catch (Exception ex)
            {
                StringBuilder sbParam = new StringBuilder();
                int sqlIndex = 1;
                foreach (string eSQL in lstCmdText)
                {
                    sbParam.Append("<SQL" + sqlIndex.ToString()).Append("><![CDATA[").Append(eSQL);
                    sbParam.Append("]]></SQL" + sqlIndex.ToString() + ">");
                    sqlIndex++;
                }
                MsErrLog msErrorLog = new MsErrLog {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = sbParam.ToString()
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
                sbParam = null;
                return new List<string> { 
                    "-2",
                    "保存失败:详情请参见错误日志!"                    
                };
            }
        }


        /// <summary>
        /// 是否变更上级菜单
        /// </summary>
        /// <param name="menuId">当前操作的菜单Id</param>
        /// <param name="newUpMenuId">新上级菜单Id</param>
        /// <returns></returns>
        private List<bool> isAlterUpMenu(string menuId, string newUpMenuId)
        {
            bool bAlter = false;

            //获取当前操作菜单以及上级菜单两个消息对象
            StringBuilder sbCmdText = new StringBuilder();
            sbCmdText.Append("SELECT PUR_B1_10 AS MENUID,PUR_B1_30 AS LAYCODE FROM PUR_B1 ");
            if (string.IsNullOrEmpty(newUpMenuId))
                sbCmdText.Append("WHERE PUR_B1_10 IN (").Append(menuId).Append(")");
            else
                sbCmdText.Append("WHERE PUR_B1_10 IN (").Append(menuId + "," + newUpMenuId).Append(")");

            MsMenu msCurMenu = null;
            MsMenu msUpMenu = null;
            string tempMenuId = string.Empty;
            try
            {
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    while (iReader.Read())
                    {
                        tempMenuId = iReader["MENUID"].ToString();
                        if (tempMenuId == menuId)
                        {
                            msCurMenu = new MsMenu
                            {
                                menuId = menuId,
                                layCode = iReader["LAYCODE"].ToString()
                            };
                        }
                        else if (tempMenuId == newUpMenuId)
                        {
                            msUpMenu = new MsMenu
                            {
                                menuId = newUpMenuId,
                                layCode = iReader["LAYCODE"].ToString()
                            };
                        }
                    }
                }

                if (null == msCurMenu || (!string.IsNullOrEmpty(newUpMenuId)) && (null == msUpMenu)) throw new Exception();

            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;

                return new List<bool> { false, false };
            }

            #region 校验逻辑
            string curMenuLayCode = msCurMenu.layCode;
            int curCodeLen = curMenuLayCode.Length;
            string newUpMenuLayCode = (null != msUpMenu) ? msUpMenu.layCode : string.Empty;

            if (string.IsNullOrEmpty(newUpMenuId)) //变更为1级菜单
            {
                if (msCurMenu.layCode.Length == 6) bAlter = false;
                else bAlter = true;
            }
            else
            {
                if (curCodeLen == 6) bAlter = true;
                else
                {
                    string oriUpMenuCode = curMenuLayCode.Substring(0, (curMenuLayCode.Length - 6));
                    if (oriUpMenuCode == newUpMenuLayCode) bAlter = false;
                    else bAlter = true;
                }
            }
            #endregion

            sbCmdText = null;
            msCurMenu = null;
            msUpMenu = null;
            return new List<bool> { true, bAlter };
        }
        #endregion


        
    }
}
